package com.lwj.utils.excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.lwj.utils.CollectionTool;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author lwj
 * @since 2020-08-03
 * @description 使用easyExcel来导出xlsx的工具类
 */
public class EasyExcelUtil {

    private static final Logger log = LoggerFactory.getLogger(EasyExcelUtil.class);

    private static final int MAXROWS = 1000000;
    private List<WriteHandler> writeHandlerList=new ArrayList<>();

    public List<WriteHandler> getWriteHandlerList() {
        return writeHandlerList;
    }

    public void setWriteHandlerList(List<WriteHandler> writeHandlerList) {
        this.writeHandlerList = writeHandlerList;
    }

    public EasyExcelUtil() {
    }


    public EasyExcelUtil(WriteCellStyle headWriteCellStyle, WriteCellStyle contentWriteCellStyle) {
        this.writeHandlerList.add(new HorizontalCellStyleStrategy(headWriteCellStyle,contentWriteCellStyle));
    }

    public static EasyExcelUtil defaultEasyExcelUtil(String waterMark ){
        EasyExcelUtil easyExcelUtil = new EasyExcelUtil();
        HorizontalCellStyleStrategy defaultHorizontalCellStyleStrategy = getDefaultHorizontalCellStyleStrategy();
        easyExcelUtil.addWriteHandler(defaultHorizontalCellStyleStrategy);
        easyExcelUtil.addWriteHandler(new WaterMarkStrategy(waterMark));
        return easyExcelUtil;
    }
    public void addWriteHandler(WriteHandler writeHandler){
        this.writeHandlerList.add(writeHandler);
    }
    /**
     * 获取默认表头内容的样式
     * @return
     */
    private static HorizontalCellStyleStrategy getDefaultHorizontalCellStyleStrategy(){
        /** 表头样式 **/
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景色（浅灰色）
        // 可以参考：https://www.cnblogs.com/vofill/p/11230387.html
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        // 字体大小
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 10);
        headWriteCellStyle.setWriteFont(headWriteFont);
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        /** 内容样式 **/
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 内容字体样式（名称、大小）
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontName("宋体");
        contentWriteFont.setFontHeightInPoints((short) 10);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        //设置内容垂直居中对齐
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置内容水平居中对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //设置边框样式
//        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
//        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
//        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
//        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        // 头样式与内容样式合并
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    /**
     * 导出,单个Sheet，最大支持xlsx格式sheet的行数
     * @param response
     * @param data
     * @param fileName
     * @param sheetName
     * @param clazz
     * @throws Exception
     */
    public  void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class clazz) throws Exception {
        long exportStartTime = System.currentTimeMillis();
        log.info("报表导出Size: "+data.size()+"条。");
        ExcelWriterBuilder builder = builder(fileName, response);
        ExcelWriterSheetBuilder sheet = builder.head(clazz).excelType(ExcelTypeEnum.XLSX).sheet(replaceSheetName(sheetName));
        sheet.doWrite(data);
        System.out.println("报表导出结束时间:"+ new Date()+";写入耗时: "+(System.currentTimeMillis()-exportStartTime)+"ms" );
    }
    public ExcelWriterBuilder builder(String fileName, HttpServletResponse response) throws Exception {
        ExcelWriterBuilder excelWriterBuilder = EasyExcelFactory.write(getOutputStream(fileName, response)).inMemory(true);
        for (int i = 0; i < writeHandlerList.size(); i++) {
            excelWriterBuilder.registerWriteHandler(writeHandlerList.get(i));
        }
        return excelWriterBuilder;
    }
    /**
     * 单一类型大批量数据导出，适用于超过一百万的数据，需要分多个sheet页来导出。自动分页
     * @author lwj
     * @createDate 2020-11-16
     * @param response
     * @param data  查询结果
     * @param fileName 导出文件名称
     * @param clazz 映射实体class类
     * @param <T>  查询结果类型
     * @throws Exception
     */
    public <T> void writeExcel(HttpServletResponse response, List<T> data, String fileName, Class clazz) throws Exception {
        long exportStartTime = System.currentTimeMillis();
        log.info("报表导出Size: "+data.size()+"条。");

        List<List<T>> lists = CollectionTool.splitList(data,MAXROWS); // 分割的集合
        ExcelWriterBuilder builder = builder(fileName, response);
        ExcelWriterBuilder excelWriterBuilder = builder.head(clazz).excelType(ExcelTypeEnum.XLSX);
        ExcelWriter excelWriter = excelWriterBuilder.build();
        ExcelWriterSheetBuilder excelWriterSheetBuilder;
        WriteSheet writeSheet;
        for (int i =1;i<=lists.size();i++){
            excelWriterSheetBuilder = new ExcelWriterSheetBuilder(excelWriter);
            excelWriterSheetBuilder.sheetNo(i);
            excelWriterSheetBuilder.sheetName("sheet"+i);
            writeSheet = excelWriterSheetBuilder.build();
            excelWriter.write(lists.get(i-1),writeSheet);
        }
        excelWriter.finish();
        System.out.println("报表导出结束时间:"+ new Date()+";写入耗时: "+(System.currentTimeMillis()-exportStartTime)+"ms" );
    }

    public  OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
        fileName = URLEncoder.encode(replaceSheetName(fileName), "UTF-8");
        //  response.setContentType("application/vnd.ms-excel"); // .xls
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); // .xlsx
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
        return response.getOutputStream();
    }




    /**
     * 单纯写入，适用于手动分页
     * @author lwj
     * @param excelWriter
     * @param <T>
     * @throws Exception
     */
    public <T> void writeOnly(ExcelWriter excelWriter,SheetInfo sheetInfo) {
        long exportStartTime = System.currentTimeMillis();
        log.info("报表"+sheetInfo.getSheetName()+"写入Size: "+sheetInfo.getData().size()+"条。");
        ExcelWriterSheetBuilder excelWriterSheetBuilder = new ExcelWriterSheetBuilder(excelWriter);
        excelWriterSheetBuilder.sheetNo(sheetInfo.getSheetIndex());
        excelWriterSheetBuilder.sheetName(replaceSheetName(sheetInfo.getSheetName()));
        List<WriteHandler> writeHandlerList = sheetInfo.getWriteHandlerList();
        writeHandlerList.stream().forEach(p->excelWriterSheetBuilder.registerWriteHandler(p));
        WriteSheet writeSheet = excelWriterSheetBuilder.build();
        writeSheet.setClazz(sheetInfo.getC());
        excelWriter.write(sheetInfo.getData(),writeSheet);
        log.info("报表"+sheetInfo.getSheetName()+"写入耗时: "+(System.currentTimeMillis()-exportStartTime)+"ms" );
    }

    public void writeSheets( HttpServletResponse response,String fileName,List<? extends SheetInfo> sheetInfos){
        try {
            // 2 获取EasyExcel写入类
            ExcelWriter excelWriter = EasyExcelFactory.write(getOutputStream(fileName, response)).inMemory(true).build();
            for (int i = 0; i < sheetInfos.size(); i++) {
                SheetInfo sheetInfo = sheetInfos.get(i);
                // 3.1 写入模板一
                writeOnly(excelWriter,sheetInfo);
            }
            excelWriter.finish();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public   String replaceSheetName(String sheetName) {
        if (org.apache.commons.lang3.StringUtils.isBlank(sheetName)) {
            return "";
        }
        String regex = "[/:*\"?\\\\<>|]";
        return sheetName.replaceAll(regex, "-");
    }
}
